package cn.hexcloud.dga.governance.mapper;

import cn.hexcloud.dga.governance.bean.GovernanceAssessTable;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 表治理考评情况 Mapper 接口
 * </p>
 *
 * @author jiangdan7
 * @since 2023-09-11
 */
@Mapper
public interface GovernanceAssessTableMapper extends BaseMapper<GovernanceAssessTable> {
    @Select("select assess_date,table_name,schema_name,tec_owner,\n" +
            "\t\t\t\tavg(if(governance_type = 'SPEC',assess_score,null))*10 score_spec_avg,\n" +
            "\t\t\t\tavg(if(governance_type = 'STORAGE',assess_score,null))*10 score_storage_avg,\n" +
            "\t\t\t\tavg(if(governance_type = 'CALC',assess_score,null))*10 score_calc_avg,\n" +
            "\t\t\t\tavg(if(governance_type = 'QUALITY',assess_score,null))*10 score_quality_avg,\n" +
            "\t\t\t\tavg(if(governance_type = 'SECURITY',assess_score,null))*10 score_security_avg,\n" +
            "\t\t\t\tsum(if(assess_score != 10,1,0)) problem_num,\n" +
            "\t\t\t\tnow() create_time\n" +
            "from governance_assess_detail\n" +
            "where assess_date = #{assessDate} and is_assess_exception = '0' \n" +
            "group by assess_date,table_name,schema_name,tec_owner")
    public List<GovernanceAssessTable> calcScoreByDetail(@Param("assessDate") String assessDate);
}
